package com.fdm.model.DAO;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import com.fdm.model.accounts.InterestRateDecorator;
import com.fdm.model.accounts._Account;
import com.fdm.model.exceptions.DAOException;
import com.fdm.model.properties.SQLProperties;

public class SQLDAO implements _AccountDAO
{
	private static Connection connection;
	private static SQLProperties properties = new SQLProperties();
	private RSAccountBuilder accountBuilder = new RSAccountBuilder();
		
	@Override
	public _Account retrieve(int accountNumber) throws DAOException
	{
		_Account found = null;
		try {
			PreparedStatement pStatement = 
					createPreparedStatement(properties.getSelect());
			pStatement.setInt(1, accountNumber);
			ResultSet result = pStatement.executeQuery();
			if(result.next()){
				found = accountBuilder.buildAccount(result);
			}
			result.close();
			pStatement.close();
		} 
		catch (SQLException e) {
		}
		finally{
			closeConnection();
		}
		return found;
	}

	@Override
	public void delete(int accountNumber) throws DAOException 
	{
		try{
			PreparedStatement pStatement =
					createPreparedStatement(properties.getDelete());
			pStatement.setInt(1, accountNumber);
			pStatement.executeUpdate();
			pStatement.close();
		} catch (SQLException e) {
			throw new DAOException();
		}
		finally{
			closeConnection();
		}
	}

	@Override
	public void update (int accountNumber,Double newValue) 
			throws DAOException
	{
		try{
			PreparedStatement pStatement =
					createPreparedStatement(properties.getUpdate());
			pStatement.setDouble(1, newValue);
			pStatement.setInt(2, accountNumber);
			pStatement.executeUpdate();
			pStatement.close();
		} 
		catch (SQLException e) 
		{
			throw new DAOException();
		}
		finally	{
			closeConnection();
		}
	}

	@Override
	public void create(_Account newAccount) throws DAOException 
	{
		try{
			double interestRate = 0;
			PreparedStatement pStatement =
					createPreparedStatement(properties.getInsert());
			pStatement.setInt(1, newAccount.getAccountNumber());
			pStatement.setString(2, newAccount.getName());
			pStatement.setString(3,newAccount.getAccountType());
			pStatement.setDouble(4,newAccount.getBalance());
			if (newAccount.getAccountType().equals("SAVINGS"))
			{
				interestRate = ((InterestRateDecorator)newAccount).getInterestRate();
			}
			pStatement.setDouble(5,interestRate);
			pStatement.setDouble(6, newAccount.getOverDraftLimit());
			pStatement.executeUpdate();
			pStatement.close();
		}
		catch (SQLException e) 
		{
			throw new DAOException();
		}
		finally{
			closeConnection();
		}		
	}

	@Override
	public List<_Account> retrieveAll() throws DAOException 
	{
		List<_Account> accounts = new ArrayList<_Account>();
		try {
			PreparedStatement pStatement = 
					createPreparedStatement(properties.getSelectAll());
			ResultSet result = pStatement.executeQuery();

			while(result.next())
			{
				accounts.add(accountBuilder.buildAccount(result));
			}	
			result.close();
			pStatement.close();
		} 
		catch (SQLException e) {
			throw new DAOException();
		}
		finally{
			closeConnection();
		}
		return accounts;
	}

	@Override
	public String getStorageType() 
	{
		return "SQL";
	}
	
	private void closeConnection() 
	{
		try {
			connection.commit();
			if (!connection.isClosed()){
				connection.close();
			}
		} catch (SQLException e) {
		}
	}
	
	private void createConnection() throws SQLException, DAOException
	{
    	Context initContext;
		try {
			initContext = new InitialContext();
	    	Context envContext  = (Context)initContext.lookup("java:/comp/env");
	    	DataSource ds = (DataSource)envContext.lookup("jdbc/alainOracle");
	    	connection = ds.getConnection();
	    	
		} catch (NamingException e) {
			throw new DAOException();
		}
	}
	
	private PreparedStatement createPreparedStatement(String sql) 
			throws SQLException, DAOException
	{
		createConnection();
		return connection.prepareStatement(sql);
	}

	@Override
	public int getMaxAccountNumber() throws DAOException {
		int max=0;
		try {
			PreparedStatement pStatement = createPreparedStatement(properties.getMax());
			ResultSet result = pStatement.executeQuery();
			result.next();
			max = result.getInt("MAX(ACCOUNTNUMBER)");
			result.close();
			pStatement.close();
		} 
		catch (SQLException e) {
			max=0;
		}
		finally{
			closeConnection();
		}
		return max;
	}
}
